﻿CREATE PROCEDURE [dbo].[Text_BookTree_RssNews]
AS
BEGIN
	--DECLARE @news TABLE
	--(
	--	[BookTreeId] BIGINT,
	--	[BookId] UNIQUEIDENTIFIER
	--)

	--INSERT INTO @news
	--SELECT t.[BookTreeId], t.[BookId]
	--FROM [dbo].[text_BookTree] AS t
	--WHERE t.[PublishedDate] IS NOT NULL AND t.[Published] = 1

	--IF NOT EXISTS (SELECT * FROM @news)
	--BEGIN
	--	RETURN
	--END

	--DECLARE @maxHeadLevel TABLE
	--(
	--	[BookId] UNIQUEIDENTIFIER,
	--	[HeadLevel] TINYINT
	--)

	--INSERT INTO @maxHeadLevel ([BookId], [HeadLevel])
	--SELECT DISTINCT n.[BookId], (SELECT MAX([Level]) FROM [dbo].[text_BookTree] WHERE [BookId] = n.[BookId] AND [IsHead] = 1)
	--FROM @news AS n

	--SELECT DISTINCT [BookId], [Author], [CoverUrl]
	--FROM [dbo].[text_Book]
	--WHERE [BookId] IN (
	--	SELECT DISTINCT [BookId] FROM @news
	--)

	--SELECT DISTINCT t.[Text]
	--				,	t.[BookId]
	--				, c.[Url]
	--FROM [dbo].[text_BookTree] AS t
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]
	--WHERE [ParentBookTreeId] IS NULL AND [BookId] IN (
	--	SELECT DISTINCT [BookId] FROM @news
	--)

	--DECLARE @chapters TABLE
	--(
	--	[BookTreeId] BIGINT
	--)

	--INSERT @chapters
	--SELECT n.[BookTreeId]
	--FROM @news AS n
	--INNER JOIN @maxHeadLevel AS ml ON n.[BookId] = ml.[BookId]
	--INNER JOIN  [dbo].[text_BookTree] AS t ON n.[BookTreeId] = t.[BookTreeId]
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]
	--WHERE [ParentBookTreeId] IS NOT NULL AND [Level] = ml.[HeadLevel] - 1 AND NOT EXISTS (SELECT * FROM [dbo].[text_BookTree] WHERE [ParentBookTreeId] = t.[BookTreeId] AND [IsHead] = 1 AND [Published] = 0)

	--SELECT DISTINCT t.[Text], t.[BookId], c.[Url], t.[PublishedDate], t.[BookTreeId]
	--FROM @chapters AS ch
	--INNER JOIN  [dbo].[text_BookTree] AS t ON ch.[BookTreeId] = t.[BookTreeId]
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]

	--SELECT DISTINCT t.[Text], t.[BookId], c.[Url], t.[PublishedDate], t.[ParentBookTreeId], t.[LeftKey]
	--FROM @chapters AS ch
	--INNER JOIN  [dbo].[text_BookTree] AS t ON ch.[BookTreeId] = t.[ParentBookTreeId]
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]

	DECLARE @start DATETIME = CAST('2013-05-06 0:0:0' AS DATETIME)
	DECLARE @end   DATETIME = CAST('2013-05-07 0:0:0' AS DATETIME)
	
	--DECLARE @news TABLE
	--(
	--	[BookTreeId] BIGINT,
	--	[BookId] UNIQUEIDENTIFIER,
	--	[ChapterBookTreeId] BIGINT,
	--	[PublishedDate] DATETIME
	--)

	--INSERT INTO @news ([BookTreeId], [BookId], [ChapterBookTreeId], [PublishedDate])
	--SELECT t.[BookTreeId], t.[BookId], NULL, t.[PublishedDate]
	--FROM [dbo].[text_BookTree] AS t
	--WHERE t.[PublishedDate] IS NOT NULL AND t.[Published] = 1 AND (@start IS NULL OR DATEDIFF(n, @start, [PublishedDate]) > 0) AND (@end IS NULL OR DATEDIFF(n, @end, [PublishedDate]) < 0)


	--IF NOT EXISTS (SELECT * FROM @news)
	--BEGIN
	--	RETURN
	--END

	--DECLARE @maxHeadLevel TABLE
	--(
	--	[BookId] UNIQUEIDENTIFIER,
	--	[ChapterLevel] TINYINT,
	--	[SectionLevel] TINYINT
	--)

	--INSERT INTO @maxHeadLevel ([BookId], [SectionLevel])
	--SELECT DISTINCT n.[BookId], (SELECT MAX([Level]) FROM [dbo].[text_BookTree] WHERE [BookId] = n.[BookId] AND [IsHead] = 1)
	--FROM @news AS n

	--UPDATE @maxHeadLevel
	--SET [ChapterLevel] = [SectionLevel] - 1


	--UPDATE n
	--SET [ChapterBookTreeId] = CASE [Level] WHEN ml.[SectionLevel] 
	--				THEN [ParentBookTreeId] 
	--				ELSE	CASE [Level] WHEN ml.[ChapterLevel]
	--								THEN n.[BookTreeId]
	--								ELSE NULL
	--							END
	--			END
	--FROM @news AS n
	--INNER JOIN  [dbo].[text_BookTree] AS t ON n.[BookTreeId] = t.[BookTreeId]
	--INNER JOIN @maxHeadLevel AS ml ON n.[BookId] = ml.[BookId]
	--WHERE t.[Level] BETWEEN ml.[ChapterLevel] AND ml.[SectionLevel]



	--DELETE FROM @news
	--WHERE [ChapterBookTreeId] IS NULL

	--INSERT INTO @news ([BookTreeId], [BookId], [ChapterBookTreeId], [PublishedDate])
	--SELECT t.[BookTreeId], t.[BookId], t.[BookTreeId], t.[PublishedDate]
	--FROM [dbo].[text_BookTree] AS t
	--WHERE t.[BookTreeId] IN (SELECT [ChapterBookTreeId] FROM @news)

	--INSERT INTO @news ([BookTreeId], [BookId], [ChapterBookTreeId], [PublishedDate])
	--SELECT t.[BookTreeId], t.[BookId], t.[ParentBookTreeId], t.[PublishedDate]
	--FROM [dbo].[text_BookTree] AS t
	--WHERE t.[ParentBookTreeId] IN (SELECT [ChapterBookTreeId] FROM @news) AND t.[IsHead] = 1

	--DELETE FROM @news
	--WHERE [ChapterBookTreeId] IN (SELECT [ChapterBookTreeId] FROM @news WHERE [PublishedDate] IS NULL) 



	--DELETE FROM @news
	--WHERE [ChapterBookTreeId] NOT IN (SELECT [ChapterBookTreeId]
	--	FROM @news
	--	GROUP BY [ChapterBookTreeId]
	--	HAVING (@start IS NULL OR DATEDIFF(n, @start, MAX([PublishedDate])) > 0) AND (@end IS NULL OR DATEDIFF(n, @end, MAX([PublishedDate])) < 0))


	-------------- SELECTS

	--SELECT DISTINCT [BookId], [Author], [CoverUrl]
	--FROM [dbo].[text_Book]
	--WHERE [BookId] IN (
	--	SELECT DISTINCT [BookId] FROM @news
	--)

	--SELECT DISTINCT t.[Text]
	--				,	t.[BookId]
	--				, c.[Url]
	--FROM [dbo].[text_BookTree] AS t
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]
	--WHERE [ParentBookTreeId] IS NULL AND t.[BookId] IN (
	--	SELECT DISTINCT [BookId] FROM @news
	--)

	--SELECT DISTINCT t.[Text], t.[BookId], c.[Url], t.[PublishedDate], t.[BookTreeId]
	--FROM [dbo].[text_BookTree] AS t 
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]
	--WHERE t.[BookTreeId] IS NULL AND [BookId] IN (
	--	SELECT DISTINCT [ChapterBookTreeId] FROM @news
	--)

	--SELECT DISTINCT t.[Text], t.[BookId], c.[Url], t.[PublishedDate], t.[ParentBookTreeId], t.[LeftKey]
	--FROM [dbo].[text_BookTree] AS t 
	--INNER JOIN [dbo].[text_BookTree_Cache] AS c ON t.[BookTreeId] = c.[BookTreeId]
	--WHERE t.[ParentBookTreeId] IS NULL AND [BookId] IN (
	--	SELECT DISTINCT [ChapterBookTreeId] FROM @news
	--)




END